# coding:utf-8
import traceback

import pymysql

config = {
    'host': '127.0.0.1',
    'port': 3306,
    'user': 'root',
    'password': '1',
    'db': 'upush2_test',
    'charset': 'utf8mb4',
    'cursorclass': pymysql.cursors.DictCursor,
    'connect_timeout': 10
}


def search_one(connection, sql, param):
    try:

        with connection.cursor() as cursor:
            # 执行sql语句，插入记录

            cursor.execute(sql, param)

            return cursor.fetchone()

    except BaseException, ex:
        print traceback.format_exc()
        print "执行失败, 原因:" + ex.message
        return None
    finally:
        connection.commit()


def do_sql(connection, sql, param):
    try:

        with connection.cursor() as cursor:
            # 执行sql语句，插入记录

            cursor.execute(sql, param)

    except BaseException, ex:
        print traceback.format_exc()
        print "执行失败,原因:" + ex.message
    finally:
        connection.commit()


def allready_inserted(connection, contact_code):
    sql = "SELECT 1 FROM upush2_contact  where code = %s"

    result = search_one(connection=connection, sql=sql, param=contact_code)

    if result is not None:
        return True
    else:
        return False


def remove_app(connection, group_code):
    sql = "DELETE FROM upush2_contact_group  WHERE code = %s"
    do_sql(connection=connection, sql=sql, param=group_code)


def insert_contact(connection, _code, _email, _is_chief, _mobile, _name,
                   _group_id,
                   _language, _contact_type):
    sql = "INSERT INTO upush2_test.upush2_contact (code, created, email," \
          " is_chief, job_title, mobile, " \
          "name, source, updated, weixin, group_id, language, contact_type) " \
          "VALUES (%s, null, %s, %s, null, %s, %s, null, null, null, %s, %s, %s);"

    do_sql(connection=connection, sql=sql,
           param=(
               _code, _email, _is_chief, _mobile, _name, _group_id, _language,
               _contact_type))


def get_group_code_by_id(connection, group_code):
    sql = "SELECT id  FROM upush2_contact_group  where code = %s"

    return search_one(connection=connection, sql=sql, param=group_code)


def allready_group_inserted(connection, group_code):
    sql = "SELECT 1 FROM upush2_contact_group cg where cg.code = %s"

    result = search_one(connection=connection, sql=sql, param=group_code)

    if result is not None:
        return True
    else:
        return False


def insert_group(connection, group_code, name, type):
    sql = "INSERT INTO upush2_contact_group " \
          "(code, name, type) VALUES (%s, %s, %s);"

    do_sql(connection=connection, sql=sql, param=(group_code, name, type))


def deal_init():
    try:
        # Connect to the database
        connection = pymysql.connect(**config)

        f = '/Users/keel2008/Desktop/info_chief.txt'

        batch = 0
        count = 0

        with open(f, 'r') as _f:
            while 1:
                count += 1
                line = _f.readline()
                if not line:
                    break
                (_gc, _gname, _code, _name, _email,
                 _mobile) = line.split(
                    '---')

                if allready_group_inserted(connection=connection,
                                           group_code=_gc):
                    pass
                else:

                    insert_group(connection=connection, group_code=_gc,
                                 name=_gname,
                                 type='CUSTOMER')

                if allready_inserted(connection=connection,
                                     contact_code=_code):
                    pass
                else:
                    _result = get_group_code_by_id(connection=connection,
                                                   group_code=_gc)

                    if _result is None:
                        print "GCode not found: %s " % _gc
                    else:
                        _group_id = _result['id']

                        # 添加一个联络人
                        insert_contact(connection=connection,
                                       _code=_code,
                                       _email=_email,
                                       _is_chief='true',
                                       _mobile=_mobile,
                                       _name=_name,
                                       _group_id=_group_id,
                                       _language='ZH_CN',
                                       _contact_type="CHIEF")

                        batch += 1

                        if batch > 200:
                            connection.commit()
                            batch = 0

                            print "提交一次,count=%d" % count
        print count
    except BaseException, ex:
        print ex.message

    finally:
        # pass
        if connection is not None:
            try:
                connection.commit()
                connection.close()
            except BaseException, ex:
                print ex.message


deal_init()
